﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StoragePeriod.IDAL.FoodTesting;
using StoragePeriod.Model.FoodTesting;
using StoragePeriod.DBUtility;
using System.Data;
using System.Data.SqlClient;

namespace StoragePeriod.SqlServerDAL.FoodTesting
{
    /// <summary>
    /// Vc含量操作
    /// </summary>
    public class D_VcData:I_VcData
    {
        /// <summary>
        /// 获取Vc含量检查实体
        /// </summary>
        /// <param name="eVcData">查询ID</param>
        /// <returns>返回对应的实体</returns>
        public E_VcData GetVcDataEntityById(E_VcData eVcData)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select * from VcData where id=" + eVcData.id);
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                E_VcData Entity = new ModelHandler<E_VcData>().FillModel(dt.Rows[0]);
                return Entity;
            }
            return null;
        }

        /// <summary>
        /// 根据原料ID和检测时间 删除检测记录
        /// </summary>
        /// <param name="eVcData">要删除的检测记录</param>
        /// <returns></returns>
        public int RemoveByVcTime(E_VcData eVcData)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from VcData where RawId="+eVcData.RawId+" and VcTime=CAST('"+eVcData.VcTime.ToString()+"' as datetime)");
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 根据原料ID 和检测时间 获取所有的检测记录
        /// </summary>
        /// <param name="eVcData">要获取的检测记录</param>
        /// <returns></returns>
        public List<E_VcData> GetVcDataListByVcTime(E_VcData eVcData)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select * from VcData where RawId=" + eVcData.RawId + " and VcTime=cast('"+eVcData.VcTime.ToString()+"' as datetime)");
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                List<E_VcData> EntityList = new ModelHandler<E_VcData>().FillModel(dt);
                return EntityList;
            }
            return null;
        }

        /// <summary>
        /// 获取最后一次检测的数据
        /// </summary>
        /// <returns></returns>
        public E_VcData GetModelByTimeDesc(E_VcData eVcData)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select top 1 * from VcData order by VcTime desc");
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                List<E_VcData> EntityList = new ModelHandler<E_VcData>().FillModel(dt);
                return EntityList.First();
            }
            return null;
        }

        /// <summary>
        /// 删除某一次检测记录
        /// </summary>
        /// <param name="eVcData">要删除的检测记录</param>
        /// <returns></returns>
        public int RemoveByFoodNum(E_VcData eVcData)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from VcData where RawId=" + eVcData.RawId + " and VcTime=CAST('" + eVcData.VcTime.ToString() + "' as datetime) and FoodNum=" + eVcData.FoodNum);
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(E_VcData eVcData)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into VcData(");
            strSql.Append("RawId,RawName,Vc_C,Vc_V,Vc_V1,Vc_V2,Vc_2,Vc_2V,Vc_A,Vc_W,Vc_T,Vc_Result,VcTime,FormulaTID,FormulaVCID,FoodNum)");
            strSql.Append(" values (");
            strSql.Append("@RawId,@RawName,@Vc_C,@Vc_V,@Vc_V1,@Vc_V2,@Vc_2,@Vc_2V,@Vc_A,@Vc_W,@Vc_T,@Vc_Result,@VcTime,@FormulaTID,@FormulaVCID,@FoodNum)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@RawId", SqlDbType.Int,4),
					new SqlParameter("@RawName", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_C", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_V", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_V1", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_V2", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_2", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_2V", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_A", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_W", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_T", SqlDbType.VarChar,50),
					new SqlParameter("@Vc_Result", SqlDbType.VarChar,50),
					new SqlParameter("@VcTime", SqlDbType.DateTime),
					new SqlParameter("@FormulaTID", SqlDbType.Int,4),
					new SqlParameter("@FormulaVCID", SqlDbType.Int,4),
					new SqlParameter("@FoodNum", SqlDbType.Int,4)};
            parameters[0].Value = eVcData.RawId;
            parameters[1].Value = eVcData.RawName;
            parameters[2].Value = eVcData.Vc_C;
            parameters[3].Value = eVcData.Vc_V;
            parameters[4].Value = eVcData.Vc_V1;
            parameters[5].Value = eVcData.Vc_V2;
            parameters[6].Value = eVcData.Vc_2;
            parameters[7].Value = eVcData.Vc_2V;
            parameters[8].Value = eVcData.Vc_A;
            parameters[9].Value = eVcData.Vc_W;
            parameters[10].Value = eVcData.Vc_T;
            parameters[11].Value = eVcData.Vc_Result;
            parameters[12].Value = eVcData.VcTime;
            parameters[13].Value = eVcData.FormulaTID;
            parameters[14].Value = eVcData.FormulaVCID;
            parameters[15].Value = eVcData.FoodNum;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
    }
}
